
/**********************************************************************
***********************************************************************
***********************************************************************
Treatment 
***********************************************************************
***********************************************************************
**********************************************************************/

/**********************************************************************
Add Treatment
***********************************************************************/
if exists 
	(
		select * from dbo.sysobjects 
		where id = object_id(N'AddTreatment') 
		and OBJECTPROPERTY(id, N'IsProcedure') = 1
	)
	drop procedure AddTreatment
GO

create procedure AddTreatment
	@name nvarchar(50),
	@pinyin nvarchar(50),
	@type int,
	@isquantified int,
	@measurementUnits nvarchar(50),
	@limitvalues nvarchar(50),
	@choicesdescription nvarchar(2000),
	@intro nvarchar(4000),
	@desp nvarchar(max),
	@uploaddate datetime,
	@classPatientId int,
	@PatientCount int,
	@GeneralName nvarchar(50),
	@Merchant nvarchar(50),
	@Frequency nvarchar(50)
as
insert into [treatment]
	(
		treatmentname,
		firstsymbolofname,
		treatmenttypeid,
		isquantified,
		measurementunits,
		limitvalues,
		choicesdescription,
		Introduction,
		description,      
		uploaddate,
		classPatientId,
		PatientCount,
		GeneralName,
		Merchant,
		Frequency
	)
values
	(
		@name,
		@pinyin,
		@type,
		@isquantified,
		@measurementunits,
		@limitvalues,
		@choicesdescription,
		@intro,
		@desp,
		@uploaddate,
		@classPatientId,
		@PatientCount,
		@GeneralName,
		@Merchant,
		@Frequency
	)
   
GO

/**********************************************************************
UpdateTreatment
***********************************************************************/
if exists 
	(
		select * from dbo.sysobjects 
		where id = object_id(N'UpdateTreatment') 
		and OBJECTPROPERTY(id, N'IsProcedure') = 1
	)
	drop procedure UpdateTreatment
GO

create procedure UpdateTreatment
	@id int,
	@name nvarchar(50),
	@pinyin nvarchar(50),
	@type int,
	@isquantified int,
	@measurementUnits nvarchar(50),
	@limitvalues nvarchar(50),
	@choicesdescription nvarchar(2000),
	@intro nvarchar(4000),
	@desp nvarchar(max),
	@updatetime datetime,
	@classPatientId int,
	@GeneralName nvarchar(50),
	@Merchant nvarchar(50),
	@ClassPatientName nvarchar(50),
	@Frequency nvarchar(50)
as
	UPDATE 
		[treatment]
	SET 
		[treatmentname] = @name
		,[Introduction] = @intro
		,[description] = @desp
		,[treatmenttypeid] = @type
		,[isquantified] = @isquantified
		,[measurementunits] = @measurementunits
		,[limitvalues] = @limitvalues
		,[choicesdescription] = @choicesdescription
		,[firstsymbolofname] = @pinyin
		,[uploaddate] = @updatetime
		,[classPatientId] = @classPatientId
		,[GeneralName] = @GeneralName 
		,[Merchant] = @Merchant
		,[ClassPatientName] = @ClassPatientName
		,[Frequency] = @Frequency
	WHERE 
		[id]=@id

GO

/**********************************************************************
UpdateTreatmentPatientCount
***********************************************************************/
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'UpdateTreatmentPatientCount') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure UpdateTreatmentPatientCount

GO

create procedure UpdateTreatmentPatientCount
	@id int,
	@Num int
as
	UPDATE 
		[Treatment]
	SET 
		[PatientCount] = [PatientCount] + @Num
	WHERE 
		[id]=@id
   
GO


/**********************************************************************
  GetTreatment
**********************************************************************/	 
if exists 
	(
		select * from dbo.sysobjects 
		where id = object_id(N'GetTreatment') 
		and OBJECTPROPERTY(id, N'IsProcedure') = 1
	)
	drop procedure GetTreatment
GO

create procedure GetTreatment
	@id int
as
	select 
		'Id' = [id],
		'Name' = [treatmentname],
		'Pinyin' = [firstsymbolofname],
		'Intro' = [Introduction],
		'Type' = [treatmenttypeid],
		'Isquantified' = [isquantified],
		'MeasurementUnits' = [measurementunits],
		'LimitValues' = [limitvalues],
		'ChoicesDescription' = [choicesdescription],
		'Desp' = [description],
		'UpdateTime' = [uploaddate],
		'PatientId' = [classPatientId],
		[GeneralName],
		[PatientCount],
		[Merchant],
		[Frequency]
	from 
		[treatment]
	where 
		[id]=@id

GO

/**********************************************************************
  DelTreatment
**********************************************************************/	 
if exists 
	(
		select * from dbo.sysobjects 
		where id = object_id(N'DelTreatment') 
		and OBJECTPROPERTY(id, N'IsProcedure') = 1
	)
	drop procedure DelTreatment
GO

create procedure DelTreatment
	@condstr nvarchar(512)
as
	DECLARE @strSQL VARCHAR(600)
	set @strSQL = 'delete from [treatment] where ' 
			+ @condstr
	exec(@strSQL)
GO


--region [dbo].[GetTreatmentList]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetTreatmentList]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetTreatmentList]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetTreatmentList]
GO

CREATE PROCEDURE [dbo].[GetTreatmentList]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT(id) 
FROM [Treatment]
	'
IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT *
FROM
	( SELECT 
	[Id],
	''Name'' = [TreatmentName],
	''Intro'' = [Introduction],
	''Type'' = [TreatmentTypeId],
	''Pinyin'' = [firstsymbolofname],
	''UpdateTime'' = [uploaddate],
	[GeneralName],
	[PatientCount],
	ROW_NUMBER() OVER ( ORDER BY ' + @OrderByExpression + ' ) AS SerialNumber
	FROM 
		[Treatment] 
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END

SET @SQL = @SQL + ') AS tblTmp  
	WHERE tblTmp.SerialNumber > '
	+ STR((@PageIndex-1)*@PageSize) + ' AND 
	tblTmp.SerialNumber <= '
	+ STR(@PageIndex*@PageSize)

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetTreatmentListByDisease]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetTreatmentListByDisease]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetTreatmentListByDisease]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetTreatmentListByDisease]
GO

CREATE PROCEDURE [dbo].[GetTreatmentListByDisease]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT([Treatment].[id]) 
FROM
	[Treatment], [DiseaseTreatment], [Disease]
WHERE
	[Treatment].[Id] = [DiseaseTreatment].[TreatmentId] AND
	[Disease].[Id] = [DiseaseTreatment].[DiseaseId] 
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT *
FROM
	( SELECT 
	''Id'' = [Treatment].[id],
	''Name'' = [TreatmentName],
	''Intro'' = [Treatment].[Introduction],
	''Type'' = [TreatmentTypeId],
	''Pinyin'' = [Treatment].[firstsymbolofname],
	''UpdateTime'' = [Treatment].[uploaddate],
	[Treatment].[PatientCount],
	ROW_NUMBER() OVER ( ORDER BY ' + @OrderByExpression + ' ) AS SerialNumber
FROM
	[Treatment], [DiseaseTreatment], [disease]
WHERE
	[Treatment].[Id] = [DiseaseTreatment].[TreatmentId] AND
	[Disease].[Id] = [DiseaseTreatment].[DiseaseId] 
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
END

SET @SQL = @SQL + ') AS tblTmp  
	WHERE tblTmp.SerialNumber > '
	+ STR((@PageIndex-1)*@PageSize) + ' AND 
	tblTmp.SerialNumber <= '
	+ STR(@PageIndex*@PageSize)

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetTreatmentListWithPatientInfo]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetTreatmentListWithPatientInfo]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetTreatmentListWithPatientInfo]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetTreatmentListWithPatientInfo]
GO

CREATE PROCEDURE [dbo].[GetTreatmentListWithPatientInfo]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT([Treatment].[id]) 
FROM
	[Treatment], [UserInfo]
WHERE
	[Treatment].[ClassPatientId] = [UserInfo].[Id]
	'
IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT *
FROM
	( SELECT 
	''Id'' = [Treatment].[id],
	''Name'' = [TreatmentName],
	''Intro'' = [Introduction],
	''Type'' = [TreatmentTypeId],
	''Pinyin'' = [firstsymbolofname],
	''UpdateTime'' = [Treatment].[uploaddate],
	''PatientId'' = [ClassPatientId],
	''PatientName'' = [UserInfo].[UserName],
	''PatientAvatar'' = [UserInfo].[Avatar],
	[GeneralName],
	[PatientCount],
	ROW_NUMBER() OVER ( ORDER BY ' + @OrderByExpression + ' ) AS SerialNumber
	FROM
		[Treatment], [UserInfo]
	WHERE
		[Treatment].[ClassPatientId] = [UserInfo].[Id]
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
END

SET @SQL = @SQL + ') AS tblTmp  
	WHERE tblTmp.SerialNumber > '
	+ STR((@PageIndex-1)*@PageSize) + ' AND 
	tblTmp.SerialNumber <= '
	+ STR(@PageIndex*@PageSize)

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetTreatmentListByDiseaseWithPatientInfo]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetTreatmentListByDiseaseWithPatientInfo]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetTreatmentListByDiseaseWithPatientInfo]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetTreatmentListByDiseaseWithPatientInfo]
GO

CREATE PROCEDURE [dbo].[GetTreatmentListByDiseaseWithPatientInfo]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT([Treatment].[id]) 
FROM
	[Treatment], [DiseaseTreatment], [Disease]
WHERE
	[Treatment].[Id] = [DiseaseTreatment].[TreatmentId] AND
	[Disease].[Id] = [DiseaseTreatment].[DiseaseId] 
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT TOP ' + STR(@pagesize) + '
	''Id'' = [Treatment].[id],
	''Name'' = [TreatmentName],
	''Intro'' = [Introduction],
	''Type'' = [TreatmentTypeId],
	''Pinyin'' = [Treatment].[firstsymbolofname],
	''UpdateTime'' = [Treatment].[uploaddate],
	''PatientId'' = [ClassPatientId],
	''PatientName'' = [UserInfo].[UserName],
	''PatientAvatar'' = [UserInfo].[Avatar],
	[GeneralName],
	[Treatment].[PatientCount]
FROM
	[Treatment], [DiseaseTreatment], [disease], [UserInfo]
WHERE
	[Treatment].[Id] = [DiseaseTreatment].[TreatmentId] AND
	[Disease].[Id] = [DiseaseTreatment].[DiseaseId] AND
	[Treatment].[ClassPatientId] = [UserInfo].[Id] 
	'

IF @PageIndex > 1
BEGIN
SET @SQL = @SQL + '
	AND [Treatment].[id] < 
	( 
	SELECT MIN([id])  
	FROM ( 
	SELECT TOP 
		' + STR((@PageIndex-1)*@PageSize) + ' [Treatment].[id] 
	FROM
		[Treatment], [DiseaseTreatment], [Disease]
	WHERE
		[Treatment].[Id] = [DiseaseTreatment].[TreatmentId] AND
		[Disease].[Id] = [DiseaseTreatment].[DiseaseId] 
	' 

	IF @WhereCondition <> ''
	BEGIN
		SET @SQL = @SQL + ' AND ' + @WhereCondition
	END

	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + '
	ORDER BY
		' + @OrderByExpression + ') AS tblTmp ) '
	END
	ELSE
	BEGIN
		SET @SQL = @SQL + ') AS tblTmp ) '
	END

	IF @WhereCondition <> ''
	BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
	END
	
	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + '
	ORDER BY
		' + @OrderByExpression 
	END	
END
ELSE 
BEGIN
	IF @WhereCondition <> ''
	BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
	END
	
	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + ' 
	ORDER BY
		' + @OrderByExpression 
	END	
END

EXEC sp_executesql @SQL

GO
--endregion


/***********************************************************************
************************************************************************
TreatmentType
************************************************************************
***********************************************************************/

/**********************************************************************
AddTreatmentType
***********************************************************************/  
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'AddTreatmentType') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure AddTreatmentType
GO


create procedure AddTreatmentType
   @name nvarchar(50)
   ,@parentid  int
as
    insert into [treatmenttype](
        [parentid]
        ,[treatmenttypename]
    )values(
        @parentid,
        @name
    )
     
Go


/**********************************************************************
DelTreatmentType
***********************************************************************/
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'DelTreatmentType') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure DelTreatmentType
GO


create procedure [DelTreatmentType]
   @condstr nvarchar(512)
as
   DECLARE @strSQL VARCHAR(600)

   set @strSQL = '
   delete from [treatmenttype]
   where ' + @condstr

   exec(@strSQL)

GO


/**********************************************************************
UpdateTreatmentType
***********************************************************************/  
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'UpdateTreatmentType') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure UpdateTreatmentType
GO


create procedure UpdateTreatmentType
    @id  int
   ,@name nvarchar(50)
   ,@parentId int
as
   update [treatmenttype] set
      [treatmenttypename] = @name
     ,[parentid] = @parentid
	where
      [id] = @id
GO

/**********************************************************************
GetTreatmentType
***********************************************************************/
	 
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'GetTreatmentType') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure GetTreatmentType
GO

create procedure GetTreatmentType
   @id int
as
   select 
        'Id' = [id],
        'Name' = [TreatmentTypeName],
        'ParentId' = [parentid]
   from 
        [treatmentType]
   where [id]=@id
   
GO


/**********************************************************************
GetTreatmentTypeList
***********************************************************************/  
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'GetTreatmentTypeList') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure GetTreatmentTypeList
GO


create procedure [GetTreatmentTypeList]
   @condstr  nvarchar(512)
as
   DECLARE @strSQL VARCHAR(600)
   set @strSQL = 'select 
           ''Id'' = [id]
          ,''Name'' = [treatmenttypename]
          ,''ParentId'' = [parentid]
   from
		  [treatmenttype]'
	
	 if @condstr <> ''
		 begin
		 	set @strSQL = @strSQL + ' where ' + @condstr
		 end
   
   exec(@strSQL)
GO


--region [dbo].[GetTreatmentStatDosage]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetTreatmentStatDosage]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetTreatmentStatDosage]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetTreatmentStatDosage]
GO

CREATE PROCEDURE [dbo].[GetTreatmentStatDosage]
	@TreatmentId int
AS

SELECT 
	'PatientCount' = COUNT([PatientCurrentTreatment].[DataValue]),
	[PatientCurrentTreatment].[DataValue]
FROM	
	[PatientCurrentTreatment]
WHERE 
	[PatientCurrentTreatment].[TreatmentId] = @TreatmentId 
Group by
	[PatientCurrentTreatment].[DataValue]
Order by
	[PatientCount]
GO
--endregion
